import cx_Oracle
from sqlalchemy import create_engine, text
from tabulate import tabulate  # 用于格式化输出，如果没有安装可以用 pip install tabulate

# 设置连接参数
username = "sys"
password = "123456"
host = "10.1.161.137"
port = "1521"
sid = "ORCL"

# 要分析的数据库列表
schemas = ["BAYONET_BASICS", "BAYONET_BUSSINESS", "BAYONET_DYNAMIC"]


def create_oracle_connection():
    dsn = cx_Oracle.makedsn(host, port, sid=sid)
    return cx_Oracle.connect(
        user=username,
        password=password,
        dsn=dsn,
        mode=cx_Oracle.SYSDBA
    )


try:
    # 创建连接
    connection = create_oracle_connection()
    cursor = connection.cursor()

    for schema in schemas:
        print(f"\n{'=' * 80}")
        print(f" 数据库: {schema} ".center(80, '='))
        print(f"{'=' * 80}")

        # 查询该schema下的所有表
        cursor.execute(f"""  
            SELECT TABLE_NAME   
            FROM ALL_TABLES   
            WHERE OWNER = '{schema}'  
            ORDER BY TABLE_NAME  
        """)

        tables = cursor.fetchall()
        print(f"\n找到 {len(tables)} 个表")

        # 为每个表显示前5个表名
        if tables:
            print("\n部分表名示例:")
            for i, (table_name,) in enumerate(tables[:5], 1):
                print(f"{i}. {table_name}")

                # 为每个schema选择1-2个表详细分析
            sample_tables = [tables[0][0]]  # 第一个表
            if len(tables) > 5:
                sample_tables.append(tables[5][0])  # 第六个表(如果存在)

            for table_name in sample_tables:
                print(f"\n表 '{table_name}' 的结构:")

                # 查询表结构
                cursor.execute(f"""  
                    SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE  
                    FROM ALL_TAB_COLUMNS  
                    WHERE OWNER = '{schema}' AND TABLE_NAME = '{table_name}'  
                    ORDER BY COLUMN_ID  
                """)

                columns = cursor.fetchall()
                headers = ["列名", "数据类型", "长度", "可为空"]
                table_data = [[col[0], col[1], col[2], col[3]] for col in columns]
                print(tabulate(table_data, headers, tablefmt="grid"))

                # 查询表中的行数
                try:
                    cursor.execute(f"SELECT COUNT(*) FROM {schema}.{table_name}")
                    row_count = cursor.fetchone()[0]
                    print(f"表中共有 {row_count} 行数据")

                    # 如果表中有数据，显示前3行样本数据
                    if row_count > 0:
                        cursor.execute(f"SELECT * FROM {schema}.{table_name} WHERE ROWNUM <= 3")
                        sample_data = cursor.fetchall()
                        if sample_data:
                            # 获取列名
                            col_names = [desc[0] for desc in cursor.description]
                            print("\n数据样本:")
                            print(tabulate(sample_data, col_names, tablefmt="grid"))
                except Exception as e:
                    print(f"查询数据时出错: {str(e)}")
        else:
            print(f"数据库 {schema} 中没有找到表")

            # 查询三个数据库之间的关系(基于外键约束)
    print("\n\n数据库间的关系分析 (基于外键约束):")
    cursor.execute("""  
        SELECT a.OWNER AS SCHEMA_NAME, a.TABLE_NAME, a.CONSTRAINT_NAME,   
               c.TABLE_NAME AS REFERENCED_TABLE, c.OWNER AS REFERENCED_SCHEMA  
        FROM ALL_CONSTRAINTS a  
        JOIN ALL_CONSTRAINTS c ON a.R_CONSTRAINT_NAME = c.CONSTRAINT_NAME  
        WHERE a.CONSTRAINT_TYPE = 'R'  
        AND a.OWNER IN ('BAYONET_BASICS', 'BAYONET_BUSSINESS', 'BAYONET_DYNAMIC')  
        AND c.OWNER IN ('BAYONET_BASICS', 'BAYONET_BUSSINESS', 'BAYONET_DYNAMIC')  
        AND a.OWNER != c.OWNER  
        ORDER BY a.OWNER, a.TABLE_NAME  
    """)

    relations = cursor.fetchall()
    if relations:
        print("\n跨数据库外键关系:")
        headers = ["源Schema", "源表", "约束名", "引用表", "引用Schema"]
        print(tabulate(relations, headers, tablefmt="grid"))
    else:
        print("未找到数据库之间的外键关系")

        # 关闭连接
    cursor.close()
    connection.close()

except Exception as e:
    print(f"❌ 操作失败: {str(e)}")
